import sqlite3
from typing import List, Tuple, Optional, Any
from contextlib import contextmanager
from utils.paths import DATA_DIR


class Database:
    def __init__(self):
        """
        初始化数据库连接池
        :param db_path: SQLite 数据库文件路径
        """
        self.db_path = DATA_DIR / "bot.db"
        # 创建连接测试是否能打开数据库
        with self._get_conn() as conn:
            pass  # 验证连接可用

    @contextmanager
    def _get_conn(self):
        """获取数据库连接的上下文管理器"""
        conn = sqlite3.connect(self.db_path, check_same_thread=False)
        try:
            yield conn
        except Exception as e:
            conn.rollback()
            raise e
        else:
            conn.commit()  # 只有 SELECT 不需要提交，但 commit 无害
        finally:
            conn.close()

    def execute(self, sql: str, params: Tuple[Any, ...] = ()) -> int:
        """
        执行写操作（INSERT, UPDATE, DELETE），返回影响行数

        :param sql: 要执行的 SQL 语句
        :param params: 占位符参数，元组格式，防止 SQL 注入
        :return: 影响的行数
        """
        if not sql.strip().upper().startswith(("INSERT", "UPDATE", "DELETE")):
            raise ValueError("execute() 仅用于写操作（INSERT/UPDATE/DELETE）")

        with self._get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute(sql, params)
            return cursor.rowcount

    def fetch_one(self, sql: str, params: Tuple[Any, ...] = ()) -> Optional[Tuple[Any, ...]]:
        """
        查询单条记录

        :param sql: SELECT 语句
        :param params: 参数
        :return: 单条记录元组，无结果返回 None
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute(sql, params)
            return cursor.fetchone()

    def fetch_all(self, sql: str, params: Tuple[Any, ...] = ()) -> List[Tuple[Any, ...]]:
        """
        查询所有匹配记录

        :param sql: SELECT 语句
        :param params: 参数
        :return: 结果列表，每行是一个元组
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute(sql, params)
            return cursor.fetchall()

    def execute_script(self, script: str) -> None:
        """
        执行多条 SQL（用于建表、初始化数据等）

        :param script: 包含多条 SQL 的字符串
        """
        with self._get_conn() as conn:
            cursor = conn.cursor()
            cursor.executescript(script)


db = Database()

